<?php
  class myQuery{
    public static function getReceivables($con,$customer_iden,$owner_iden,$date_from=null,$date_to=null){        
      $query = 
        'SELECT lt.iden2,d.document_date,i.days_due,date_add(d.document_date, interval i.days_due day) due_date,concat(i.invoice_type,\'-\',i.invoice_id) invoice_id,'.
        'sum(lt.debit-lt.credit) AS total '.
        'FROM document d '.
        'join log_transaction lt '.
        'on d.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'INVOICE\' '.
        'join invoice i '.
        'on lt.iden2=i.invoice_iden '.
        'and lt.type2=\'INVOICE\' '.
        'where i.customer_iden = \''.$customer_iden.'\' '.
        ' and d.owner_iden = \''.$owner_iden.'\'';
        if (($date_from) && ($date_to))
          $query .=' and exists(select * from document d1 where d1.document_date >= \''.$date_from.'\' and d1.document_date <= \''.$date_to.'\' and d1.document_iden = lt.iden2)';
#based on due date
#          $query .=' and exists(select * from document d1 where date_add(d1.document_date,interval i.days_due day) >= \''.$date_from.'\' and date_add(d1.document_date,interval i.days_due day) <= \''.$date_to.'\' and d1.document_iden = lt.iden2)';
      $query .= ' group by lt.iden2 ';
      $query .= ' having total<>0 ';
      $query .= ' order by d.document_date ';
      $statement = $con->prepare($query);
      $statement->execute();
      return $statement->fetchAll(PDO::FETCH_ASSOC);

    }
    
    public static function getCustomerTotalBalance($con,$customer_iden){
      $query = 
        'SELECT sum(lt.debit-lt.credit) AS total '.
        'FROM document d '.
        'join log_transaction lt '.
        'on d.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'INVOICE\' '.
        'join invoice i '.
        'on lt.iden2=i.invoice_iden '.
        'and lt.type2=\'INVOICE\' '.
        'where i.customer_iden = \''.$customer_iden.'\' ';
      $statement = $con->prepare($query);
      $statement->execute();
      $result = $statement->fetchAll(PDO::FETCH_ASSOC);
      return $result[0]['total'];

    }
    
    public static function getInvoiceTransactions($con,$invoice_iden){
      $query = 
        'SELECT lt.type3,sum(lt.debit) debit,sum(lt.credit) credit '.
        'FROM document d '.
        'join log_transaction lt on '.
        'd.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'INVOICE\' '.
        'and lt.type3<> \'AMOUNT\' '.
        'join invoice i on lt.iden2=i.invoice_iden '.
        'where i.invoice_iden = \''.$invoice_iden.'\' '.
        'group by lt.type3';
      $statement = $con->prepare($query);
      $statement->execute();
      $invoice_balance_tr =  $statement->fetchAll(PDO::FETCH_ASSOC);
      return $invoice_balance_tr;

    }
    
    public static function getInvoiceBalance($con,$invoice_iden){
      $query = 
        'SELECT lt.iden2,d.document_date,i.days_due,date_add(d.document_date, interval i.days_due day) due_date,concat(i.invoice_type,\'-\',i.invoice_id) invoice_id,'.
        'sum(lt.debit-lt.credit) AS total '.
        'FROM document d '.
        'join log_transaction lt '.
        'on d.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'INVOICE\' '.
        'join invoice i '.
        'on lt.iden2=i.invoice_iden '.
        'and lt.type2=\'INVOICE\' '.
        'where i.invoice_iden = \''.$invoice_iden.'\'';
      $query .= 'group by lt.iden2 ';
      $query .= 'having total<>0 ';
      $query .= 'order by d.document_date ';
      $statement = $con->prepare($query);
      $statement->execute();
      $invoice_balance =  $statement->fetchAll(PDO::FETCH_ASSOC);
      return $invoice_balance[0];

    }
    
    public static function getCashAdvances($con,$customer_iden,$owner_iden){    
      $query = 
        'SELECT lt.iden2,d.document_date,concat(p.payment_type,\'-\',p.payment_number) payment_id,'.
        'sum(lt.debit-lt.credit) AS total '.
        'FROM document d '.
        'join log_transaction lt '.
        'on d.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'PAYMENT\' '.
        'join payment p '.
        'on lt.iden2=p.payment_iden '.
        'and lt.type2=\'PAYMENT\' '.
        'where p.customer_iden = \''.$customer_iden.'\' and p.transaction_type=\'CASH\''.
        ' and d.owner_iden = \''.$owner_iden.'\'';
      $query .= 'group by lt.iden2 ';
      $query .= 'having total>0 ';
      $query .= 'order by d.document_date ';
      $statement = $con->prepare($query);
      $statement->execute();
      return $statement->fetchAll(PDO::FETCH_ASSOC);

    }
    
    public static function getCheckAdvances($con,$customer_iden,$owner_iden){    
      $query = 
        'SELECT lt.iden2,d.document_date,concat(p.payment_type,\'-\',p.payment_number) payment_id,concat(pc.bank_initials,\'#\',pc.check_number) check_id,pc.check_date,'.
        'sum(lt.debit-lt.credit) AS total '.
        'FROM document d '.
        'join log_transaction lt '.
        'on d.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'PAYMENT\' '.
        'join payment p '.
        'on lt.iden2=p.payment_iden '.
        'and lt.type2=\'PAYMENT\' '.
        'join payment_checks pc '.
        'on pc.payment_iden = p.payment_iden '.
        'where p.customer_iden = \''.$customer_iden.'\' and p.transaction_type=\'CHECK\''.
        ' and d.owner_iden = \''.$owner_iden.'\'';
      $query .= 'group by lt.iden2 ';
      $query .= 'having total>0 ';
      $query .= 'order by d.document_date ';
      $statement = $con->prepare($query);
      $statement->execute();
      return $statement->fetchAll(PDO::FETCH_ASSOC);

    }
    
  }
